-- ============================================
-- Ⅰ. 用户触发器
-- ============================================

-- 1. 用户 uid 自动生成
CREATE OR REPLACE FUNCTION generate_uid()
RETURNS TRIGGER AS $$
BEGIN
    NEW.uid := 'U' || to_char(NEW.id, 'FM0000000000');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_generate_uid ON users;
CREATE TRIGGER trg_generate_uid
BEFORE INSERT ON users 
FOR EACH ROW
EXECUTE FUNCTION generate_uid();

-- 2. 用户头像自动生成（半分钟时间片）
DROP TRIGGER IF EXISTS trg_bump_avatar_filename ON users;
DROP FUNCTION IF EXISTS bump_avatar_filename();


-- 3. 用户关注/粉丝更新
CREATE OR REPLACE FUNCTION update_follow_fan_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users
    SET follow_count = (SELECT COUNT(*) FROM user_follow WHERE follower_id = NEW.follower_id)
    WHERE id = NEW.follower_id;

    UPDATE users
    SET fan_count = (SELECT COUNT(*) FROM user_follow WHERE followee_id = NEW.followee_id)
    WHERE id = NEW.followee_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_follow_fan_count ON user_follow;
CREATE TRIGGER trg_follow_fan_count
AFTER INSERT OR DELETE ON user_follow
FOR EACH ROW
EXECUTE FUNCTION update_follow_fan_count();

-- ============================================
-- Ⅱ. 食谱触发器
-- ============================================

-- 1. 食谱封面图片自动生成
DROP FUNCTION IF EXISTS set_recipe_cover_image();
DROP TRIGGER IF EXISTS trg_recipe_cover_image ON recipe;

-- 2. 食谱更新时间
CREATE OR REPLACE FUNCTION update_recipe_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_recipe_updated ON recipe;
CREATE TRIGGER trg_recipe_updated
BEFORE UPDATE ON recipe
FOR EACH ROW
EXECUTE FUNCTION update_recipe_updated_at();

-- 3. 食谱难度平均值更新
CREATE OR REPLACE FUNCTION update_recipe_difficulty_avg()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE recipe
    SET difficulty_avg = COALESCE(
        (SELECT ROUND(AVG(difficulty_score)::numeric,2)
         FROM recipe_difficulty_vote
         WHERE recipe_id = NEW.recipe_id),0)
    WHERE id = NEW.recipe_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_difficulty_avg_ins ON recipe_difficulty_vote;
CREATE TRIGGER trg_update_difficulty_avg_ins
AFTER INSERT OR UPDATE ON recipe_difficulty_vote
FOR EACH ROW
EXECUTE FUNCTION update_recipe_difficulty_avg();

DROP TRIGGER IF EXISTS trg_update_difficulty_avg_del ON recipe_difficulty_vote;
CREATE TRIGGER trg_update_difficulty_avg_del
AFTER DELETE ON recipe_difficulty_vote
FOR EACH ROW
EXECUTE FUNCTION update_recipe_difficulty_avg();

-- 4. 食谱点赞数自动更新
CREATE OR REPLACE FUNCTION update_recipe_likes_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE recipe 
        SET likes_count = likes_count + 1,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = NEW.recipe_id;

        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE recipe 
        SET likes_count = GREATEST(likes_count - 1, 0),
            updated_at = CURRENT_TIMESTAMP
        WHERE id = OLD.recipe_id;

        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_likes_count ON likes;
CREATE TRIGGER trigger_update_likes_count
AFTER INSERT OR DELETE ON likes
FOR EACH ROW
EXECUTE FUNCTION update_recipe_likes_count();

-- 5. 食谱收藏数自动更新
CREATE OR REPLACE FUNCTION update_recipe_favorite_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE recipe
        SET likes_count = likes_count + 1, 
            updated_at = CURRENT_TIMESTAMP
        WHERE id = NEW.recipe_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE recipe
        SET likes_count = likes_count + 1, 
            updated_at = CURRENT_TIMESTAMP
        WHERE id = OLD.recipe_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 6.更新 recipe_comment 的 like_count
CREATE OR REPLACE FUNCTION update_comment_like_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE recipe_comment
        SET like_count = like_count + 1
        WHERE id = NEW.comment_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE recipe_comment
        SET like_count = GREATEST(like_count - 1, 0)
        WHERE id = OLD.comment_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_comment_like_count ON recipe_comment_like;
CREATE TRIGGER trg_update_comment_like_count
AFTER INSERT OR DELETE ON recipe_comment_like
FOR EACH ROW
EXECUTE FUNCTION update_comment_like_count();


-- ============================================
-- Ⅲ. 步骤触发器
-- ============================================

-- 1. 步骤耗时计算
CREATE OR REPLACE FUNCTION update_duration_seconds()
RETURNS TRIGGER AS $$
BEGIN
    NEW.duration_seconds := EXTRACT(EPOCH FROM (COALESCE(NEW.end_time, CURRENT_TIMESTAMP) - NEW.start_time));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_step_time_calc ON step_time_record;
CREATE TRIGGER trg_step_time_calc
BEFORE INSERT OR UPDATE ON step_time_record
FOR EACH ROW
EXECUTE FUNCTION update_duration_seconds();

-- ============================================
-- Ⅳ. 食材触发器
-- ============================================

-- 食材图片自动生成
CREATE OR REPLACE FUNCTION generate_ingredient_image_path()
RETURNS TRIGGER AS $$
BEGIN
    NEW.image_path := '/uploads/recipes/ingredients/ING' || to_char(NEW.id, 'FM00000') || '.jpg';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_generate_ingredient_image_path ON ingredient;
CREATE TRIGGER trg_generate_ingredient_image_path
BEFORE INSERT ON ingredient
FOR EACH ROW
EXECUTE FUNCTION generate_ingredient_image_path();

-- ============================================
-- Ⅴ. 草稿触发器
-- ============================================

CREATE OR REPLACE FUNCTION update_draft_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_recipe_draft_updated ON recipe_draft;
CREATE TRIGGER trg_recipe_draft_updated
BEFORE UPDATE ON recipe_draft
FOR EACH ROW
EXECUTE FUNCTION update_draft_updated_at();

-- ============================================
-- Ⅵ. 消息触发器
-- ============================================

-- 1. 食谱点赞消息（已删除，不再触发）
DROP TRIGGER IF EXISTS trg_message_recipe_like ON likes;
DROP FUNCTION IF EXISTS create_recipe_like_message();

-- 2. 食谱评论消息
CREATE OR REPLACE FUNCTION create_recipe_comment_message()
    RETURNS TRIGGER AS $$
DECLARE
    recipe_owner BIGINT;
    commenter_username VARCHAR;
    recipe_title VARCHAR;
BEGIN
    SELECT user_id, title INTO recipe_owner, recipe_title FROM recipe WHERE id = NEW.recipe_id;
    SELECT username INTO commenter_username FROM users WHERE id = NEW.user_id;

    IF recipe_owner IS NOT NULL AND recipe_owner <> NEW.user_id THEN
        INSERT INTO user_message(user_id, from_user_id, type, reference_id, content)
        VALUES (
                   recipe_owner,
                   NEW.user_id,
                   'recipe_comment',
                   NEW.recipe_id,
                    ' 评论了你的食谱：' || NEW.content
               );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_message_recipe_comment ON recipe_comment;
CREATE TRIGGER trg_message_recipe_comment
    AFTER INSERT ON recipe_comment
    FOR EACH ROW
EXECUTE FUNCTION create_recipe_comment_message();

-- 3. 评论点赞消息
CREATE OR REPLACE FUNCTION create_comment_like_message()
    RETURNS TRIGGER AS $$
DECLARE
    comment_owner BIGINT;
    liker_username VARCHAR;
    recipe_title VARCHAR;
    comment_content TEXT;
    target_recipe_id BIGINT;
BEGIN
    SELECT rc.user_id, rc.content, rc.recipe_id
    INTO comment_owner, comment_content, target_recipe_id
    FROM recipe_comment rc
    WHERE rc.id = NEW.comment_id;

    SELECT u.username INTO liker_username
    FROM users u
    WHERE u.id = NEW.user_id;

    SELECT r.title INTO recipe_title
    FROM recipe r
             JOIN recipe_comment rc ON r.id = rc.recipe_id
    WHERE rc.id = NEW.comment_id;

    IF comment_owner IS NOT NULL AND comment_owner <> NEW.user_id THEN
        INSERT INTO user_message(user_id, from_user_id, type, reference_id, content)
        VALUES (
                   comment_owner,
                   NEW.user_id,
                   'comment_like',
                   target_recipe_id,
                   liker_username || ' 点赞了你的评论：' || comment_content
               );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_message_comment_like ON recipe_comment_like;
CREATE TRIGGER trg_message_comment_like
    AFTER INSERT ON recipe_comment_like
    FOR EACH ROW
EXECUTE FUNCTION create_comment_like_message();

-- 4. 被关注消息
CREATE OR REPLACE FUNCTION create_follow_message()
    RETURNS TRIGGER AS $$
DECLARE
    follower_username VARCHAR;
BEGIN
    SELECT username INTO follower_username FROM users WHERE id = NEW.follower_id;
    IF NEW.follower_id <> NEW.followee_id THEN
        INSERT INTO user_message(user_id, from_user_id, type, reference_id, content)
        VALUES (
                   NEW.followee_id,
                   NEW.follower_id,
                   'follow',
                   NEW.follower_id,
                    ' 关注了你'
               );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_message_follow ON user_follow;
CREATE TRIGGER trg_message_follow
    AFTER INSERT ON user_follow
    FOR EACH ROW
EXECUTE FUNCTION create_follow_message();

-- 5. 收藏消息
CREATE OR REPLACE FUNCTION create_favorite_message()
    RETURNS TRIGGER AS $$
DECLARE
    recipe_owner BIGINT;
    favoriter_username VARCHAR;
    recipe_title VARCHAR;
BEGIN
    SELECT user_id, title INTO recipe_owner, recipe_title FROM recipe WHERE id = NEW.recipe_id;
    SELECT username INTO favoriter_username FROM users WHERE id = NEW.user_id;

    IF recipe_owner IS NOT NULL AND recipe_owner <> NEW.user_id THEN
        INSERT INTO user_message(user_id, from_user_id, type, reference_id, content)
        VALUES (
                   recipe_owner,
                   NEW.user_id,
                   'favorite',
                   NEW.recipe_id,
                    ' 收藏了你的食谱《' || COALESCE(recipe_title,'未知食谱') || '》'
               );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_message_favorite ON favorite;
CREATE TRIGGER trg_message_favorite
    AFTER INSERT ON favorite
    FOR EACH ROW
EXECUTE FUNCTION create_favorite_message();

--评论时间
-- 创建触发函数
CREATE OR REPLACE FUNCTION set_recipe_comment_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    -- 如果插入时 created_at 为 NULL，则设置为当前时间
    IF NEW.created_at IS NULL THEN
        NEW.created_at := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_recipe_comment_created_at
BEFORE INSERT ON recipe_comment
FOR EACH ROW
EXECUTE FUNCTION set_recipe_comment_timestamp();


-- ============================================
-- Ⅶ. 测试用户
-- ============================================

INSERT INTO users (username, password_hash, email, avatar)
VALUES ('root', crypt('root', gen_salt('bf')), 'root@example.com','/uploads/users/avatar/U0000000001.jpg')
ON CONFLICT DO NOTHING;
